package com.group3.utils;

import com.group3.domain.PunchRecord;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * @ClassName ExcelData
 * @Author WenhaoChen
 * @Date 2021/6/19 17:00
 **/
public class ExcelData {

    public List<PunchRecord> punchRecord(MultipartFile file) throws IOException {
        InputStream in = null;
        try {
            //将file转InputStream
            in = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //将InputStream转XLSX对象（即表格对象）
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
        //获取表格的第一页
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        //获取该页有多少数据
        int rowNum = xssfSheet.getLastRowNum();

        List<PunchRecord> punchRecords = new ArrayList<>();

        for (int i = 2; i <= rowNum; i++) {
            //获取当前行
            XSSFRow xssfRow = xssfSheet.getRow(i);

            String studentName = "";
            try{
                studentName = xssfRow.getCell(2).toString();//此行抛错表示后面没有有效数据
            }catch (Exception e){
                return punchRecords;
            }
            //由于学号是纯数字，默认纯数字是以double传的，写进据库会变成科学计数法格式的，需要改为以string传进来
            xssfRow.getCell(1).setCellType(CellType.STRING);

            //以此获得该行的所有单元格
            Date date = xssfRow.getCell(0).getDateCellValue();
            String studentId = xssfRow.getCell(1).toString();
            Date time = xssfRow.getCell(3).getDateCellValue();
            String equipment = xssfRow.getCell(4).toString();

            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");//date格式
            SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");//time格式

            String time1 = timeFormat.format(time);
            String date1 = dateFormat.format(date);
            String punch = date1 +" "+ time1;

            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date punchTime = null;
            try {
                punchTime = format.parse(punch);
            } catch (ParseException e) {
                e.printStackTrace();
            }
            //将数据写入实体类
            PunchRecord punchRecord = new PunchRecord();
            punchRecord.setPunchTime(punchTime);
            punchRecord.setStudentId(studentId);
            punchRecord.setStudentName(studentName);
            punchRecord.setEquipment(equipment);

            punchRecords.add(punchRecord);
        }
        return punchRecords;
    }
}
